import sys

from PyQt5.QtWidgets import QMessageBox, QWidget, QTableWidgetItem, QFileDialog, QMainWindow
from PyQt5 import QtGui
from mainWindow import Ui_MainWindow
import pymssql
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Color, Side, Border
import matplotlib.pyplot as plt
import mainWindow
from numpy import array
from copy import deepcopy


class EmployeeManagement(QMainWindow, mainWindow.Ui_MainWindow):
    def __init__(self, sql, widget):
        super().__init__()
        self.sql = sql

    def registerEmployee(self, employee_info, salary, academy):

        try:
            # 注意数据插入顺序
            self.sql.insert('salary', salary)
            self.sql.insert('academy', academy)
            # 先提交到数据库 否则会有外键错误
            self.sql.commit()
            self.sql.insert('employee', employee_info)
            self.sql.commit()
        except Exception as e:
            QMessageBox.critical(self,
                                 '错误信息',
                                 str(e))
        finally:
            QMessageBox.about(self,
                              "提示",
                              "注册成功!")

    # 是否需要加入table参数
    def searchEmployee(self, search_info=None, info=None, condition=None):
        """
        :param search_info:字典格式{'表名': True|FaLse}
        :param info:字典格式
        :param condition: 字典格式
        :return:
        """
        table = {'employee': 'employee'}
        # 复制字典但是不改变其值
        condition_with_multiple_table = deepcopy(condition)
        if condition:
            for key in condition:
                if key in ['post_id', 'department_id', 'salary_id', 'academy_id', 'status_id']:
                    new_key = key.split('_')[0] + '.' + key
                    condition_with_multiple_table.pop(key)
                    condition_with_multiple_table[new_key] = condition[key]
            condition = condition_with_multiple_table
        if search_info:
            for key in search_info:
                # 会导致传进来的post_id等参数被修改 解决办法:使用不同表的post_id或者不让键值重复即可,见上面for循环代码 在没有加employee前缀的情况下
                if search_info[key] and key != 'employee':
                    table[key] = key
                    condition_key = f'employee.{key}_id'
                    condition[condition_key] = f'{key}.{key}_id'

        try:
            self.sql.formatParam(table)
            data = self.sql.select(self.sql.param, column=info, condition=condition)
            return data
        except Exception as e:
            QMessageBox.critical(self,
                                 '错误信息',
                                 str(e))

    def updateEmployeeInfo(self, updated_table, value_dit, condition):
        """
        :param updated_table: 表名(str)
        :param value_dit: 字典格式
        :param condition: 字典格式
        :return:
        """
        # updated_table = self.generateTable(updated_table) --多个表情况
        try:
            self.sql.formatSQLString(condition)
            self.sql.formatSQLString(value_dit)
            for key in value_dit:
                value = f"{key}={value_dit[key]}"
                self.sql.update(updated_table, value, condition)
            self.sql.commit()
        except Exception as e:
            QMessageBox.critical(self,
                                 '错误信息',
                                 str(e))
        finally:
            QMessageBox.about(self, '提醒', '更新成功')

    def mappingWindowToDatabase(self, window_name, mapping_rule):
        real_condition_dict = {}
        for key in window_name:
            if key == '现役员工' or key == '历史员工':
                real_key = window_name[key]
                value = mapping_rule[real_key]
            else:
                real_key = mapping_rule[key]
                value = window_name[key]
            real_condition_dict[real_key] = value
        return real_condition_dict

    def generateTable(self, table):
        generated_table = {}
        for key in table:
            if table[key]:
                generated_table[key] = key
        return generated_table


class TableOutput():
    def __init__(self, table_widget):
        self.row_count = 0
        self.column_count = 0
        self.header = []
        self.table_widget = table_widget
        self.mapping_list = []

    def appendTableWidgetItem(self, data):
        self.getRowCount()
        row = 0
        color1 = QtGui.QColor(128, 158, 148)
        color2 = QtGui.QColor(181, 201, 192)
        self.removeAllCell()
        for dict in data:
            column = 0
            # 处理本来就存在的行
            # if self.row_count > row:
            #     pass
            # else:
            #     self.table_widget.insertRow(row)
            self.table_widget.insertRow(row)
            for key in self.mapping_list:
                item_data = str(dict[key])
                # 只能插入字符串,需要提前对数据库中的值进行转换 解决返回数据中文乱码问题 .encode('latin-1').decode('GBK')
                item = QTableWidgetItem(item_data)
                if row % 2:
                    item.setBackground(QtGui.QBrush(color1))
                else:
                    item.setBackground(QtGui.QBrush(color2))
                self.table_widget.setItem(row, column, item)
                column += 1
            row += 1
        self.row_count = row

    def getRowCount(self):
        self.row_count = self.table_widget.rowCount()

    def getColumnCount(self):
        self.column_count = self.table_widget.columnCount()

    def getColumnName(self):
        self.header = []
        column = 0
        while column < self.column_count:
            header_item = self.table_widget.horizontalHeaderItem(column)
            self.header.append(header_item.text())
            column += 1

    def exportAsExcel(self, sheet_name, export_name):
        self.getColumnCount()
        self.getRowCount()
        wb = Workbook()
        ws = wb.create_sheet(title=sheet_name)
        self.getColumnName()
        column = 1

        header_border_style = Side(border_style='medium')
        content_border_style = Side(border_style='thin')
        color1 = Color('5F9EA0')
        color2 = Color('2E8B57')

        for val in self.header:
            ws.cell(1, column, val)
            ws.cell(1, column).fill = PatternFill(patternType='solid', fgColor=Color('483D8B'))
            ws.cell(1, column).border = Border(top=header_border_style, bottom=header_border_style,
                                               right=header_border_style, left=header_border_style)
            column += 1
        for row in range(self.row_count):
            if row % 2:
                content_foreground_color = color1
            else:
                content_foreground_color = color2
            for column in range(self.column_count):
                ws.cell(row + 2, column + 1,
                        self.table_widget.item(row, column).text())
                ws.cell(row + 2, column + 1).fill = PatternFill(patternType='solid',
                                                                fgColor=content_foreground_color)
                ws.cell(row + 2, column + 1).border = Border(top=content_border_style, bottom=content_border_style,
                                                             right=content_border_style, left=content_border_style)

        wb.save(filename=export_name)

    def getFileName(self):
        file = QFileDialog.getSaveFileName(self.table_widget, "保存至", "./", "Excel文件(*.xlsx)")
        return file[0]

    def setMappingList(self, mapping_rule):
        self.getColumnCount()
        for i in range(self.column_count):
            column = self.table_widget.horizontalHeaderItem(i)
            header_name = column.text()
            # 按列的顺序生成列表 与数据库列名对应 结果为数据库列名的表
            self.mapping_list.append(mapping_rule[header_name])

    def removeAllCell(self):
        # 不可用
        # self.getRowCount()
        # self.getColumnCount()
        # for row in range(self.row_count):
        #     for column in range(self.column_count):
        #         self.table_widget.removeCellWidget(row, column)
        self.table_widget.setRowCount(0)


class Statistic():
    def __init__(self, flag):
        self.flag = flag

    def analyseAndPrintData(self, table_widget, data):
        """
        :param table_widget: QTableWidget
        :param data: dict
        :return:
        """
        table = TableOutput(table_widget)
        if self.flag is True:
            table.getRowCount()
            table.getColumnCount()
            for row in range(table.row_count):
                for column in range(table.column_count):
                    # removeCellWidget无法删除,只能设置行为0
                    table_widget.setRowCount(0)
            self.flag = False
        table.getRowCount()
        row = table.row_count
        color1 = QtGui.QColor(128, 158, 148)
        color2 = QtGui.QColor(0, 128, 0)
        for key in data:
            table_widget.insertRow(row)
            item1 = QTableWidgetItem(str(key))  # 只能插入字符串,需要提前对数据库中的值进行转换
            item2 = QTableWidgetItem(str(data[key]))
            if row % 2:
                item1.setBackground(QtGui.QBrush(color1))
                item2.setBackground(QtGui.QBrush(color1))
            else:
                item1.setBackground(QtGui.QBrush(color2))
                item2.setBackground(QtGui.QBrush(color2))
            table_widget.setItem(row, 0, item1)
            table_widget.setItem(row, 1, item2)
            row += 1
        table_widget.row_count = row


    def drawFigure(self, xlabel_name, ylabel_name, data):
        sum = 0
        plt.rcParams['font.sans-serif'] = ['SimHei']
        x, y = self.formatData(data)
        figure = plt.figure(1)
        # 柱状图
        ax1 = plt.subplot(3, 1, 1)
        ax1.set_xlabel(xlabel_name)
        ax1.set_ylabel(ylabel_name)
        ax1.set_title('统计结果(柱状图)', bbox={'facecolor':'0.8', 'pad':5})
        plt.bar(x, y)
        # 饼状图
        for i in y:
            sum += i
        if sum != 0:
            ration = [i/sum for i in y]
        else:
            return
        label = x
        explode = [0] * len(y)
        explode[0] = 0.1
        ax2 = plt.subplot(3, 1, 3)
        plt.pie(ration, explode=explode, labels=label, autopct='%1.1f%%')
        plt.title('统计结果(饼图)')
        plt.show()

    def formatData(self, data):
        x_data = []
        y_data = []
        for x in data:
            x_data.append(x)
            y_data.append(data[x])
        return x_data, y_data
